try:
	import sqlite3 as sqlite
except:
	from pysqlite2 import dbapi2 as sqlite
from option import Option
import signals
import gtk,gobject,cairo
import time


CONN=0
CHART_PAGE=0
def init():
	global CONN,CHART_PAGE
	CONN=sqlite.connect('statsdb')
	signals.connect('mode.add',on_modeadd)
	signals.connect('mode.remove',on_moderm)
	prepare_db(CONN)

def prepare_db(conn):
	cur=conn.cursor()
	qry="""
CREATE TABLE IF NOT EXISTS scores
(
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	mode_id INTEGER,
	score REAL,
	created TEXT
);"""
	cur.execute(qry)
	qry="""
CREATE TABLE IF NOT EXISTS modes
(
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT
);"""	
	cur.execute(qry)
	qry="""
CREATE TRIGGER IF NOT EXISTS modes_scores BEFORE DELETE ON modes
	FOR EACH ROW BEGIN
		DELETE FROM scores WHERE mode_id = OLD.id;
	END;
"""
	cur.execute(qry)
	qry="""
CREATE TABLE IF NOT EXISTS stats
(
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	name TEXT,
	value TEXT
);"""	
	cur.execute(qry)
	tidy_db()

	# filling stats
	
	if len(cur.execute('SELECT * FROM stats WHERE name=\'created\'').fetchall())==0:
		cur.execute('INSERT INTO stats (name,value) VALUES (\'created\',datetime(\'now\'))')

	CONN.commit()
	
def on_moderm(sig,mode,udat):
	global CONN
	CONN.execute('DELETE FROM modes WHERE name=\'%s\''%mode)
	CONN.commit()


def tidy_db():
	global CONN
	cur=CONN.cursor()
	### checking modes integrity

	### adding new modes

	for mode in Option().modes:
		rc=len(cur.execute('SELECT * FROM modes WHERE name=\'%s\''%mode).fetchall())
		if rc==0:
			cur.execute('INSERT INTO modes (name) VALUES (\'%s\') '%mode);

	### deleting removed ones

	for mode in cur.execute('SELECT * FROM modes').fetchall():
		if not mode[1] in Option().modes:
			cur.execute('DELETE FROM modes WHERE id=%i'%mode[0])
	
	CONN.commit()

def on_modeadd(sig,mode,udat):
	global CONN
	CONN.execute('INSERT INTO modes (name) VALUES (\'%s\')'%mode)
	pass

def teardown():
	global CONN
	CONN.commit()
	CONN.close()

def getModeId(mode = None):
	return CONN.execute('SELECT * FROM modes WHERE name=\'%s\''%(mode or Option().mode.options().name)).fetchone()[0]

def process_time(t):
	global CONN
	CONN.execute('INSERT INTO scores (mode_id,score,created) VALUES (%i,%f,datetime(\'now\'))'%(getModeId(),t))
	CONN.commit()

def refresh(wid=None):
	global CONN
	
	Option().windowMain.xml.get_widget('StatSolvedOverall').set_text(str(CONN.execute('SELECT count(*) FROM scores').fetchone()[0]))

	modeid=CONN.execute('SELECT id FROM modes WHERE name=\'%s\''%Option().mode.options().name).fetchone()[0]

	Option().windowMain.xml.get_widget('StatSolved').set_text(str(CONN.execute('SELECT count(*) FROM scores WHERE mode_id=%i'%modeid).fetchone()[0]))

	Option().windowMain.xml.get_widget('StatSolvedToday').set_text(str(CONN.execute('SELECT count(*) FROM scores WHERE datetime(created,\'start of day\')=datetime(\'now\',\'start of day\')').fetchone()[0]))

	age=CONN.execute('SELECT value FROM stats WHERE name=\'created\'').fetchone()[0]
	Option().windowMain.xml.get_widget('StatDatabaseAge').set_text(str(age))



### CHART GENERATION
"""
class ChartArea(gtk.DrawingArea):

    __gsignals__ = { "expose-event": "override" }

    def do_expose_event(self, event):

        cr = self.window.cairo_create()

        cr.rectangle(event.area.x, event.area.y,
                event.area.width, event.area.height)
        cr.clip()

        self.draw(cr, *self.window.get_size())

    def draw(self, cr, width, height):
	global DS
	options = {
	    'legend': {'hide': False},
	    'background': {'color': '#f0f0f0'},
	}

	chart = pycha.bar.VerticalBarChart(cr.get_target(), options)
	chart.addDataset(DS)
	chart.render()


CA=ChartArea()
DS = (
  ('dataSet 1', ((0, 1), (1, 3), (2, 2.5))),
)

def ChartCallback(wid,type):
	global CHART_PAGE
	type='Ch'+type+'()'
	eval(type)
	Option().glade.get_widget('StatsNotebook').set_current_page(CHART_PAGE)

def ChSolvedOverall():
	global DS
	ds=('datasetttt', ((0,1),(1,2),(2,3) ) )
	DS=(ds,)
"""
